package cn.cjy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.cjy.dao.UserDao;
import cn.cjy.model.User;
import cn.cjy.utils.DBUtil;
import cn.cjy.utils.MyUtil;
import cn.cjy.utils.MysqlUtil;
import cn.cjy.utils.ResultHandle;

public class UserDaoImpl implements UserDao{

	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	List<User> us = null;
	int n = 0;
	@Override
	public List<User> all() {
		List<User> users=new ArrayList<User>();
		try {
			String sql="select * from user where 1=1";
			ResultSet rs=MysqlUtil.excuteMysqlSql(sql);
			User user=null;
			while(rs.next()) {
				user=new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setPower(rs.getString("power"));
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public User one(String userid) {
		// TODO Auto-generated method stub
		String sql="select * from user where userid="+userid;
		ResultSet rs=MysqlUtil.excuteMysqlSql(sql);
		User user=null;
		try {
			if(rs.next()) {
				user=new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setPower(rs.getString("power"));
			}
		}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return user;
	}

	

	@Override
	public Integer update(User user) {
		try {
			conn = DBUtil.getConnection();
			String sql = "update user set username=?,password=?,power=? where username=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(4,user.getUsername());
			pstmt.setString(1,user.getUsername());	
			pstmt.setString(2,user.getPassword());		
			pstmt.setString(3,user.getPower());

			n = pstmt.executeUpdate();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("n=" + n);
		return n;
	}

	@Override
	public Integer add(User user) {
		// TODO Auto-generated method stub
		String sql = " insert into user (username,password,power) values ('" + user.getUsername() + "','" + user.getPassword() + "','"+"管理员"+"')";

		Integer row = MysqlUtil.excuteMysqlUpdate(sql);

		return row;
	}

	@Override
	public Integer delete(String userid) {
		// TODO Auto-generated method stub
		String sql = "delete from user where userid=" + userid;

		Integer row = MysqlUtil.excuteMysqlUpdate(sql);

		return row;
	}

	@Override
	public User login(String username, String password) {
		// TODO Auto-generated method stub
		String sql="select * from user where username=? and password=?";
		User user=MyUtil.query(sql, new ResultHandle<User>() {

			@Override
			public User handle(ResultSet rs) {
				// TODO Auto-generated method stub
				User user=new User();
				try {
					while(rs.next()) {
						user.setUserid(rs.getInt("userid"));
						user.setUsername(rs.getString("username"));
						user.setPassword(rs.getString("password"));
					}
					return user;
				}catch (SQLException e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return null;
			}
			
		}, new Object[] {username,password});
		return user;
	}

	@Override
	public Integer adminadd(User user) {
		String sql = " insert into user (username,password,power) values ('" + user.getUsername() + "','" + user.getPassword() + "','"+user.getPower()+"')";

		Integer row = MysqlUtil.excuteMysqlUpdate(sql);

		return row;
	}

	@Override
	public List<User> selectUserByUsername(String key) {
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from user where username like ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,"%".concat(key).concat("%"));
			rs = pstmt.executeQuery();
			us=new ArrayList<User>();
			while(rs.next()) {
				User user=new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));;
				user.setPower(rs.getString("power"));

				us.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return us;
	}
	
}
